﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using Estimate.Common.DBFieldTypes;

namespace Estimate.Common.Repository
{
    public class CollectionRankReportRepository : BaseRepository
    {
        public CollectionRankReportRepository(string connectionString)
            : base(connectionString, "CollectionRankReport")
        { }

        public override string GetCreateTableSql()
        {
            //string sqlcreate = String.Format("CREATE TABLE [{0}] (id bigint identity(1,1), startdate date,enddate date,type varchar(6),realtype varchar(8), sendpoints varchar(50), stations varchar(1000), scorestring text) \n", _tableName);
            //sqlcreate += String.Format("CREATE CLUSTERED INDEX [{0}_INDEX] ON [{1}] (startdate,enddate,type,realtype,sendpoints,stations) \n", _tableName, _tableName);
            //sqlcreate += String.Format("alter table [{0}] add primary key (id)", _tableName);
            //return sqlcreate;
            return "";
        }

        /// <summary>
        /// 查看台站排名表数据是否存在
        /// </summary>
        /// <param name="year">年</param>
        /// <param name="month">月</param>
        public bool Exist(int year, int month, CollectionRankType type)
        {
            string sql = String.Format("SELECT * FROM [{0}] WHERE year=@year and  month=@month and kind=@type", _tableName);
            SqlParameter[] param = new SqlParameter[3];
            param[0] = new SqlParameter("@year", year);
            param[1] = new SqlParameter("@month", month);
            param[2] = new SqlParameter("@type", type.ToString());
            return DBHelper.Exists(_connectionString, sql, param);
        }

        /// <summary>
        /// 获取台站排名表数据
        /// </summary>
        /// <param name="year">年</param>
        /// <param name="month">月</param>
        public string Get(int year, int month, CollectionRankType type)
        {
            string sql = String.Format("SELECT * FROM [{0}] WHERE year=@year and  month=@month and kind=@type", _tableName);
            SqlParameter[] param = new SqlParameter[3];
            param[0] = new SqlParameter("@year", year);
            param[1] = new SqlParameter("@month", month);
            param[2] = new SqlParameter("@type", type.ToString());
            SqlConnection connection = new SqlConnection(_connectionString);
            SqlDataReader reader = DBHelper.ExecuteReader(connection, sql, param);
            if (reader.Read())
            {
                string scorestring = reader["value"].ToString();
                connection.Close();
                return scorestring;
            }
            return "";
        }

        /// <summary>
        /// 删除台站排名表数据
        /// </summary>
        /// <param name="year">年</param>
        /// <param name="month">月</param>
        public void Delete(int year, int month, CollectionRankType type)
        {
            string sql = String.Format("delete  FROM [{0}] WHERE year=@year and  month=@month and kind=@type", _tableName);
            SqlParameter[] param = new SqlParameter[3];
            param[0] = new SqlParameter("@year", year);
            param[1] = new SqlParameter("@month", month);
            param[2] = new SqlParameter("@type", type.ToString());
            DBHelper.ExecuteSql(_connectionString, sql, param);

        }

        /// <summary>
        /// 将计算好的记录插入到CollectionRankReport表中
        /// </summary>
        /// <param name="year">年</param>
        /// <param name="month">月</param>
        /// <param name="value">成绩记录</param>
        public void Insert(int year, int month, CollectionRankType type, string value)
        {
            string sql = String.Format("INSERT INTO [{0}] (year,month,kind,value) values(@year ,@month,@type,@value)", _tableName);
            SqlParameter[] param = new SqlParameter[4];
            param[0] = new SqlParameter("@year", year);
            param[1] = new SqlParameter("@month", month);
            param[2] = new SqlParameter("@type", type.ToString());
            param[3] = new SqlParameter("@value", value);
            DBHelper.ExecuteSql(_connectionString, sql, param);
        }

    }
}

